﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ModelLayer;
using System.Data.SqlClient;

namespace CinemaDbLib
{
	public class ReservationDB
	{
		private Connection connection;
		private TicketDB ticketDB;

		public Reservation getReservationByTicket(int ticketId)
		{
			return singleWhere("ticketId=" + ticketId);
		}

		public ReservationDB()
		{
			connection = new Connection();
			ticketDB = new TicketDB();
		}

		public List<Reservation> getReservations()
		{
			return where("");
		}

		public Reservation getReservationById(int id)
		{
			return singleWhere("id = " + id);
		}

		private Reservation singleWhere(String wClause)
		{
			Reservation reservation = null;

			string query = "SELECT * FROM Reservations WHERE " + wClause;
			connection.Open();
			SqlDataReader reader = connection.Read(query);
			if (reader.Read())
			{
				reservation = new Reservation();
				reservation.Id = reader.GetInt32(0);
				reservation.Name = reader.GetString(1);
				reservation.PhoneNr = reader.GetString(2);
				reservation.Ticket = ticketDB.
					getTicketById(reader.GetInt32(3));
			}

			reader.Close();
			connection.Close();

			return reservation;
		}

		private List<Reservation> where(String wClause)
		{
			List<Reservation> reservations = new List<Reservation>();

			string query;
			if (!wClause.Equals(""))
				query = "SELECT * FROM Reservations WHERE " + wClause;
			else
				query = "SELECT * FROM Reservations";
			connection.Open();
			SqlDataReader reader = connection.Read(query);

			while (reader.Read())
			{
				Reservation reservation = new Reservation();
				reservation.Id = reader.GetInt32(0);
				reservation.Name = reader.GetString(1);
				reservation.PhoneNr = reader.GetString(2);
				reservations.Add(reservation);
				reservation.Ticket = ticketDB.
					getTicketById(reader.GetInt32(3));
			}

			reader.Close();
			connection.Close();

			return reservations;
		}

		public bool removeReservation(int id)
		{
			string query = "DELETE FROM Reservations WHERE id=" + id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}

		public int insertReservation(Reservation reservation)
		{
			int id = connection.GetMaxId("Reservations") + 1;
			string query = @"INSERT INTO Reservations (id, name, phone, "+
				"ticketId) VALUES ( " + id + ", '" + reservation.Name + "', '" 
				+ reservation.PhoneNr + "', " + reservation.Ticket.id + ")";
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			
			if (result == 1)
				return id;
			return -1;
		}

		public bool updateReservation(Reservation reservation)
		{
			string query = @"UPDATE Reservations SET name='" +
				reservation.Name + "', phone='" + reservation.PhoneNr + 
				"', ticketId=" + reservation.Ticket.id + 
				" WHERE id=" + reservation.Id;
			connection.Open();
			int result = connection.Execute(query);
			connection.Close();
			return result == 1;
		}
	}
}
